const auth = require('../middlewares/auth')
const Router = require('koa-router')
const validate = require('../utils/verify')
const Tags = require('../models/Tags')
const { fn, col, literal, QueryTypes } = require('../config/db')
const Post = require('../models/Post')
const { Op } = require('sequelize')
const { tagsList } = require('../attributes/tag')
const PostTag = require('../models/PostTag')
const { getMonthlyTagUsageORM, fillEmptyMonths } = require('../services/tagService')
const Views = require('../models/Views')
const dayjs = require('dayjs')
const router = new Router()

router.prefix('/tag')
// 获取标签列表
router['get']('/list', async (ctx) => {
  let { page, pageSize, key } = ctx.query
  const { requesttype } = ctx.request.header
  let where = {}
  if (key) {
    where.name = {
      [Op.like]: `%${key}%`,
    }
  }
  if (requesttype !== 'admin') {
    where.status = {
      [Op.ne]: 'N',
    }
  } else {
    if (page && pageSize) {
      const tagIds = await Tags.findAll({
        attributes: ['id'],
        where,
        group: 'id',
        limit: Number.parseInt(pageSize, 10), // 使用 Number.parseInt 确保有效数字
        offset: (Number.parseInt(page) - 1) * Number.parseInt(pageSize),
        raw: true,
      })
      where = {
        // 根据子查询中的id来进行分页
        id: tagIds.length ? tagIds.map((item) => item.id) : [],
        ...where,
      }
    }
  }
  const result = await Tags.findAndCountAll({
    attributes: [...tagsList, [fn('COUNT', col('posts.id')), 'articleCount']],
    include: [
      {
        model: Post,
        attributes: [],
        through: { attributes: [] }, // 忽略关联表字段
      },
    ],
    where,
    group: ['tags.id'], // 按标签ID分组
    order: [[literal('articleCount'), 'DESC']], // 按文章数排序
  })
  ctx.body = {
    code: 200,
    data: {
      total: result.count['length'],
      list: result.rows,
    },
  }
})
// 用户端获取标签详情
router['get']('/:id/detail', async (ctx) => {
  const { id } = ctx.params
  const { page = '1', pageSize = '10', sortType = 'default' } = ctx.query
  const tags = await Tags.findOne({
    where: { id },
    attributes: [...tagsList, [fn('COUNT', col('posts.id')), 'articleCount']],
    include: [
      {
        model: Post,
        attributes: [],
        through: { attributes: [] }, // 忽略关联表字段
      },
    ],
  })
  if (!tags) {
    return (ctx.body = {
      code: 404,
      data: { message: '标签不存在' },
    })
  }
  // 查询 post_tags 文章的id
  const postIds = await PostTag.findAll({
    where: { tagId: id },
    limit: Number.parseInt(pageSize, 10), // 使用 Number.parseInt 确保有效数字
    offset: (Number.parseInt(page) - 1) * Number.parseInt(pageSize),
    attributes: ['postId'],
    raw: true,
  })
  // 处理id数组
  const ids = postIds.map((item) => item.postId)
  // 查询views表统计总访问量
  const views = await Views.findOne({
    where: { postId: ids },
    attributes: [[fn('COUNT', col('views.postId')), 'views']],
    raw: true,
  })
  const order = []
  if (sortType === 'latest') {
    order.push(['createdAt', 'DESC'])
  }
  if (sortType === 'popular') {
    // 关联文章表和views表
    order.push([
      literal(`(
      SELECT COUNT(*)
      FROM views
      WHERE views.postId = posts.id
    )`),
      'DESC',
    ])
  }
  // 查询文章表
  const posts = await Post.findAll({
    where: { id: ids },
    attributes: [
      'id',
      'title',
      'coverImage',
      'createdAt',
      'excerpt',
      'likes',
      [literal('(SELECT name FROM categorys WHERE categorys.id = posts.categoryId)'), 'category'],
      [literal('(SELECT COUNT(*) FROM views WHERE views.postId = posts.id)'), 'views'],
      [
        literal(
          '(SELECT GROUP_CONCAT(tags.name) FROM tags INNER JOIN post_tags ON tags.id = post_tags.tagId WHERE post_tags.postId = posts.id)',
        ),
        'tags',
      ],
    ],
    order,
  })
  delete tags.dataValues.updatedAt
  const res = {
    ...tags.toJSON(),
    createdAt: dayjs(tags.createdAt).format('YYYY-MM-DD HH:mm:ss'),
    totalViews: views.views,
    posts,
  }
  ctx.body = {
    code: 200,
    data: res,
  }
})
// 获取标签下的文章

// 创建新标签
router['post']('/create', auth, async (ctx) => {
  const data = ctx.request.body
  if (validate.isEmpty(data.name)) {
    return (ctx.body = {
      code: 422,
      data: { message: '标签名字不能是空的' },
    })
  }
  const result = await Tags.create(data)
  if (!result) {
    throw new Error('标签创建失败')
  }
  ctx.body = {
    code: 200,
    data: true,
  }
})
router['post']('/update', auth, async (ctx) => {
  const data = ctx.request.body
  console.log(data);

  if (validate.isEmptyObj(data)) {
    return (ctx.body = {
      code: 422,
      data: { message: '更新的标签数据不能是空的' },
    })
  }
  if (validate.isEmpty(data.id)) {
    return (ctx.body = {
      code: 422,
      data: { message: '标签id不能是空的' },
    })
  }
  if (validate.isEmpty(data.name)) {
    return (ctx.body = {
      code: 422,
      data: { message: '标签名字不能是空的' },
    })
  }
  const result = await Tags.update(data, {
    where: {
      id: data.id,
    },
  })
  if (!result) {
    throw new Error('标签更新失败')
  }
  ctx.body = {
    code: 200,
    data: true,
  }
})
router['post']('/delete', auth, async (ctx) => {
  const { id } = ctx.request.body
  if (validate.isEmpty(id)) {
    return (ctx.body = {
      code: 422,
      data: { message: '标签id不能是空的' },
    })
  }
  // 查询标签是否存在
  const tag = await Tags.findOne({ where: { id } })
  if (!tag) {
    return (ctx.body = {
      code: 404,
      data: { message: '标签不存在' },
    })
  }
  // 执行删除操作
  const result = await Tags.destroy({
    where: { id },
  })
  if (!result) {
    throw new Error('')
  }
  ctx.body = {
    code: 200,
    data: true,
  }
})
router['get']('/getPostByTagId', async (ctx) => {
  let { id } = ctx.query
  const { requesttype } = ctx.request.header
  if (validate.isEmpty(id)) {
    return (ctx.body = {
      code: 422,
      data: { message: '标签id不能是空的' },
    })
  }
  const field = [
    'id',
    'title',
    'coverImage',
    'createdAt',
    'excerpt',
    [literal('(SELECT name FROM categorys WHERE categorys.id = posts.categoryId)'), 'category'],
    [literal('(SELECT COUNT(*) FROM views WHERE views.postId = posts.id)'), 'views'],
  ]
  if (requesttype === 'front') {
    field.push([
      literal(
        '(SELECT GROUP_CONCAT(tags.name) FROM tags INNER JOIN post_tags ON tags.id = post_tags.tagId WHERE post_tags.postId = posts.id)',
      ),
      'tags',
    ])
  }
  const result = await PostTag.findAll({
    where: {
      tagId: id,
    },
    attributes: [],
    include: [
      {
        model: Post,
        as: 'posts',
        attributes: [...field],
        required: false,
        // 分页
        order: [['createdAt', 'DESC']],
      },
    ],
  })
  // 处理结果，提取文章信息
  const posts = result.map((item) => {
    console.log(item)
    return item['posts'][0]
  })
  ctx.body = {
    code: 200,
    data: posts,
  }
})
// 获取标签使用趋势
router['get']('/getTagTrend', auth, async (ctx) => {
  const result = await getMonthlyTagUsageORM()
  const filledResult = fillEmptyMonths(result)
  ctx.body = {
    code: 200,
    data: filledResult,
  }
})
//
module.exports = router
